package com.example.springbootecharts.controller;

import com.alibaba.fastjson.JSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class Indexcontroller {
    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @RequestMapping(value="/index/job_name/{job_name}")
    public String indexecharts1(@PathVariable String job_name){
        String sql = "SELECT job_name,COUNT(job_name) AS job_name_num FROM jobnameworkarea WHERE job_name LIKE '%"+job_name+"%' GROUP BY job_name ORDER BY COUNT(job_name) DESC LIMIT 10;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    @RequestMapping(value="/index/workarea/{workarea}")
    public String indexecharts2(@PathVariable String workarea){
        String sql = "SELECT workarea ,COUNT(workarea) AS workarea_num FROM jobnameworkarea WHERE job_name LIKE '%"+workarea +"%' GROUP BY workarea ORDER BY workarea_num DESC LIMIT 10;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }
    @RequestMapping(value="/index/job_name_money/{job_name}")
    public String indexecharts3(@PathVariable String job_name){
        String sql = "SELECT job_name ,ROUND(AVG(minmoney),2) AS avg_minmoney,ROUND(AVG(avgmoney),2) AS avg_avgmoney,ROUND(AVG(maxmoney),2) AS avg_maxmoney FROM jobnamemoney WHERE job_name LIKE '%"+job_name+"%' GROUP BY job_name ORDER BY COUNT(job_name) DESC LIMIT 10;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    @RequestMapping(value="/index/workarea_money/{workarea}")
    public String indexecharts4(@PathVariable String workarea){
        String sql = "SELECT w.workarea,ROUND(AVG(m.minmoney),2) AS avg_minmoney,ROUND(AVG(m.avgmoney),2) AS avg_avgmoney,ROUND(AVG(m.maxmoney),2) AS avg_maxmoney FROM jobnameworkarea w RIGHT JOIN jobnamemoney m on w.id = m.id WHERE w.job_name LIKE '%"+workarea+"%' GROUP BY w.workarea ORDER BY COUNT(w.workarea) DESC LIMIT 10;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    }


